<?php
/**
 * Connect database and use table dh.
 * @return 
 * 		handler for mysql connection  
 */
function connect_db(){
	$con = mysql_connect("localhost","root","tiancai");
	if (!$con){
		  die('Could not connect: ' . mysql_error());
	}
	if (!mysql_query("use dh",$con)){
		  die('Could not use database ' . mysql_error());
	}
	return $con;
}

function getAllAttributes(){
return array(
	"amount_harvested" => "Amount Harvested",
	"amount_donated" => "Amount Donated",
	"sales" => "Sales",
	"number_planted" => "Number Planted",
	"number_transplanted" => "Number Transplanted",
	"seeds_bought" => "Seeds Bought",
	"pest_issues" => "Pest Issues",
	"weed_issues" => "Weed Issues");
}

function getAllBlocks(){
return array(
	"block_1" => "Block 1",
	"block_2" => "Block 2",
	"block_3" => "Block 3",
	"block_4" => "Block 4",
	);
}



function eatResult($resource){
	$result = array();
		if($resource){
		$next = mysql_fetch_assoc($resource);
		while($next != false){
			$result[] = $next;
			$next = mysql_fetch_assoc($resource);
		}
	}
	return $result;
}

/**
 * 
 */
function sanitize($data){
	return $data != null ? mysql_real_escape_string($data) : "0";
}
/** 
 * Return the day of the first saturday of a year,</br>
 * with 0 being the first day of the year.
 * @param $year
 * 		four digit year
 * @return
 * 		The day of the first saturday of the year.
 */
function getFirstSat($year){
	$firstDOW = date("D", strtotime("$year-01-01"));
	if($firstDOW == 'Sun') return 6;
	if($firstDOW == 'Mon') return 5;
	if($firstDOW == 'Tue') return 4;
	if($firstDOW == 'Wed') return 3;
	if($firstDOW == 'Thu') return 2;
	if($firstDOW == 'Fri') return 1;
	return 0;
}
/**
 * Get week number for a certain date.
 * @param $mysqlDay
 * 		date in mysql database
 * @param $firstSat
 * 		firstSaturday in the year
 * @return
 * 		week number for a certain date</br>
 * 		Week number starts from 0.
 */
function getWeekNumber($mysqlDay, $firstSat){
	$day = date("z", strtotime($mysqlDay));
	$day += (6 - $firstSat);
	return (int)($day / 7);
}
/**
 * Return a string describe days in a week.</br>
 * Except the 0th week, a week starts from Sunday and ends at Saturday.</br>
 * The 0th week starts at 01/01 of the year, ends at the nearest Saturday.</br>
 * 
 * @param $weekNumber
 * 		week number, start from 0
 * @param $year
 * 		four digit year
 * @return
 * 		A string formatted as mm/dd - mm/dd, showing the week's time range.
 */
function getWeekString($weekNumber, $year){
	$firstSat = getFirstSat($year);
	if($weekNumber == 0){
		return "01/01 - 01/0".($firstSat + 1);
	}
	$start = (($weekNumber - 1) * 7) + $firstSat + 1;
	$end = $start + 6;
	$startDay = DateTime::createFromFormat("z Y", "$start $year");
	$endDay = DateTime::createFromFormat("z Y", "$end $year");
	return $startDay->format("m/d") . " - " . $endDay->format("m/d");
}

/**
 * Get crop data within a range, recorded by week.</br>
 * @param $startTime
 *		start of range format= (yyyy-mm-dd)
 * @param $endTime
 *		end of range. If this isn't in the same year as $startTime, WE WILL HAVE PROBLEMS, K?
 * @param $cropId
 * 		cropId at database
 * @param $tables
 *		listing of attributes to return. If this is null, return all attributes possible. Current attributes are
 *		'harvests', 'seedPurchases', 'plantsAdded', 'plantsTransplanted', 'revenue', and 'quantitySold'
 * @return
 * 		a 2-dimentional array: $result[WeekStr][Type].
 * 		WeekStr is of the format mm/dd - mm/dd
 * 		Type can be 'harvests', 'seedPurchases', 'plantsAdded', 'plantsTransplanted', 'revenue', or 'quanititySold'
 */
function getRangeWeeklyCropData($startTime, $endTime, $cropId, $tables, $includeEmptyWeeks=false){
	$year = substr($startTime, 0, 4);
	if(strcmp($year, substr($endTime, 0, 4)) != 0) return null;
	return _getWeeklyCropData($year, $startTime, $endTime, $cropId, $tables, $includeEmptyWeeks);
}



/**
 * Get crop data within a year, recorded by week.</br>
 * @param $year
 * 		four digit year
 * @param $cropId
 * 		cropId at database
 * @param $tables
 *		listing of attributes to return. If this is null, return all attributes possible. Current attributes are
 *		'harvests', 'seedPurchases', 'plantsAdded', 'plantsTransplanted', 'revenue', and 'quantitySold'
 * @return
 * 		a 2-dimentional array: $result[WeekStr][Type].
 * 		WeekStr is of the format mm/dd - mm/dd
 * 		Type can be 'harvests', 'seedPurchases', 'plantsAdded', 'plantsTransplanted', 'revenue', or 'quanititySold'
 */
function getSingleYearWeeklyCropData($year, $cropId, $tables, $includeEmptyWeeks=false){
	$startTime = "$year-01-01";
	$endTime = "$year-12-31";
	return _getWeeklyCropData($year, $startTime, $endTime, $cropId, $tables, $includeEmptyWeeks);
}

/**
 * Get crop data within a year, recorded by week. You don't need to call this function. Use getSingleYearWeeklyCropData() or 
 * getRangeWeeklyCropData() instead.
 * @param $year
 * 		four digit year
 * @param $startTime
 *		start of range format= (yyyy-mm-dd)
 * @param $endTime
 *		end of range. If this isn't in the same year as $startTime, WE WILL HAVE PROBLEMS, K?
 * @param $cropId
 * 		cropId at database
 * @param $tables
 *		listing of attributes to return. If this is null, return all attributes possible. Current attributes are
 *		'harvests', 'seedPurchases', 'plantsAdded', 'plantsTransplanted', 'revenue', and 'quantitySold'
 * @return
 * 		a 2-dimentional array: $result[WeekStr][Type].
 * 		WeekStr is of the format mm/dd - mm/dd
 * 		Type can be 'harvests', 'seedPurchases', 'plantsAdded', 'plantsTransplanted', 'revenue', or 'quanititySold'
 */
function _getWeeklyCropData($year, $startTime, $endTime, $cropId, $tables, $includeEmptyWeeks=false){
	$firstSat = getFirstSat($year);
	$result = array();
	if(in_array('amount_harvested', $tables) || $tables == null){
		$harvests = getHarvests($startTime, $endTime, $cropId);
		foreach($harvests as $h){
			$week = getWeekNumber($h['startDate'], $firstSat);
			if(isset($result[$week]['amount_harvested'])){
				$result[$week]['amount_harvested'] += $h['quantity'];
			}else{
				$result[$week]['amount_harvested'] = $h['quantity'];
			}
		}
	}
	if(in_array('seeds_bought', $tables) || $tables == null){
		$seedPurchases = getSeedPurchases($startTime, $endTime, $cropId);
		foreach($seedPurchases as $s){
			$week = getWeekNumber($s['startDate'], $firstSat);
			if(isset($result[$week]['seeds_bought'])){
				$result[$week]['seeds_bought'] += $s['quantity'];
			}else{
				$result[$week]['seeds_bought'] = $s['quantity'];
			}
		}
	}
	if(in_array('number_planted', $tables) || $tables == null){
		$plantsAdded = getPlantsAdded($startTime, $endTime, $cropId);
		foreach($plantsAdded as $p){
			$week = getWeekNumber($p['startDate'], $firstSat);
			if(isset($result[$week]['number_planted'])){
				$result[$week]['number_planted'] += $p['quantity'];
			}else{
				$result[$week]['number_planted'] = $p['quantity'];
			}
		}
	}
	if(in_array('number_transplanted', $tables) || $tables == null){
		$plantsTransplanted = getPlantsTransplanted($startTime, $endTime, $cropId);
		foreach($plantsTransplanted as $p){
			$week = getWeekNumber($p['startDate'], $firstSat);
			if(isset($result[$week]['number_transplanted'])){
				$result[$week]['number_transplanted'] += $p['quantity'];
			}else{
				$result[$week]['number_transplanted'] = $p['quantity'];
			}
		}
	}
	if(in_array('sales', $tables) || in_array('amount_donated', $tables) || $tables == null){
		$soldDonated = getSDs($startTime, $endTime, $cropId);
		if(in_array('sales', $tables) || $tables == null){
			foreach($soldDonated as $s){
				$week = getWeekNumber($s['starttime'], $firstSat);
				if(isset($result[$week]['sales'])){
					$result[$week]['sales'] += $s['cost'];
				}else{
					$result[$week]['sales'] = $s['cost'];
				}
			}
		}
		if(in_array('amount_donated', $tables) || $tables == null){
			foreach($soldDonated as $s){
				$week = getWeekNumber($s['starttime'], $firstSat);
				if(isset($result[$week]['amount_donated'])){
					$result[$week]['amount_donated'] += $s['quantity'];
				}else{
					$result[$week]['amount_donated'] = $s['quantity'];
				}
			}
		}
	}
	if(in_array('pest_issues', $tables) || in_array('weed_issues', $tables) || $tables == null){
		$allIssues = getPestWeedIssues($startTime, $endTime, $cropId);
		if(in_array('pest_issues', $tables) || $tables == null){
			foreach($allIssues as $s){
				if($s['type'] == 0){
					$week = getWeekNumber($s['startDate'], $firstSat);
					$result[$week]['pest_issues'] = 'Yes';
				}
			}
		}
		if(in_array('weed_issues', $tables) || $tables == null){
			foreach($allIssues as $s){
				if($s['type'] == 1){
					$week = getWeekNumber($s['startDate'], $firstSat);
					$result[$week]['weed_issues'] = 'Yes';
				}
			}
		}
	}

	if(count($result) == 0) return $result;

	if($includeEmptyWeeks){
		$keys = array_keys($result);
		$max = max($keys);
		$min = min($keys);
		for($i = $min + 1; $i < $max; $i++){
			if(!isset($result[$i])){
				$result[$i] = array();
			}
		}
	}

	$result = mySort($result);

	$prettyResult = array();
	foreach(array_keys($result) as $i){
		$j = $result[$i];
		$prettyResult[getWeekString($i, $year)] = $j;
	}
	return $prettyResult;

}

function mySort($arr){

	$result = array();
	$keys = array_keys($arr);
	asort($keys);
	foreach($keys as $key){
		$result[$key] = $arr[$key];
	}
	return $result;

}


/**
 * Get daily Crop data within a period of time.
 * @param $startTime
 * 		mysql date format
 * @param $endTime
 * 		mysql date format
 * @param $cropId
 * 		cropId in database
 * @return
 * 		a 2-dimentional array: $result[Date][Type].
 * 		Date is a mysql format date.
 * 		Type can be 'harvests', 'seedPurchases', 'plantsAdded', 'plantsTransplanted', 'revenue', or 'quantitySold'
 * 		Only data with value can be returned.
 */
function getDailyCropData($startTime, $endTime, $cropId, $tables){
	if(in_array('amount_harvested', $tables) || $tables == null){
		$harvests = getHarvests($startTime, $endTime, $cropId);
		foreach($harvests as $h){
			if(isset($result[$h['startDate']]['amount_harvested'])){
				$result[$h['startDate']]['amount_harvested'] += $h['quantity'];
			}else{
				$result[$h['startDate']]['amount_harvested'] = $h['quantity'];
			}
		}
	}
	if(in_array('seeds_bought', $tables) || $tables == null){
		$seedPurchases = getSeedPurchases($startTime, $endTime, $cropId);
		foreach($seedPurchases as $s){
			if(isset($result[$s['startDate']]['seeds_bought'])){
				$result[$s['startDate']]['seeds_bought'] += $s['quantity'];
			}else{
				$result[$s['startDate']]['seeds_bought'] = $s['quantity'];
			}
		}
	}
	if(in_array('number_planted', $tables) || $tables == null){
		$plantsAdded = getPlantsAdded($startTime, $endTime, $cropId);
		foreach($plantsAdded as $p){
			if(isset($result[$p['startDate']]['number_planted'])){
				$result[$p['startDate']]['number_planted'] += $p['quantity'];
			}else{
				$result[$p['startDate']]['number_planted'] = $p['quantity'];
			}
		}
	}
	if(in_array('number_transplanted', $tables) || $tables == null){
		$plantsTransplanted = getPlantsTransplanted($startTime, $endTime, $cropId);
		foreach($plantsTransplanted as $p){
			if(isset($result[$p['startDate']]['number_transplanted'])){
				$result[$p['startDate']]['number_transplanted'] += $p['quantity'];
			}else{
				$result[$p['startDate']]['number_transplanted'] = $p['quantity'];
			}
		}
	}
	if(in_array('revenue', $tables) || in_array('quantitySold', $tables) || $tables == null){
		$soldDonated = getSDs($startTime, $endTime, $cropId);
		if(in_array('revenue', $tables) || $tables == null){
			foreach($soldDonated as $s){
				if(isset($result[$s['startDate']]['revenue'])){
					$result[$s['startDate']]['revenue'] += $s['revenue'];
				}else{
					$result[$s['startDate']]['revenue'] = $s['revenue'];
				}
			}
		}
		if(in_array('quantitySold', $tables) || $tables == null){
			foreach($soldDonated as $s){
				if(isset($result[$s['startDate']]['quantitySold'])){
					$result[$s['startDate']]['quantitySold'] += $s['quantity'];
				}else{
					$result[$s['startDate']]['quantitySold'] = $s['quantity'];
				}
			}
		}
	}
	return asort($result);
}

function getMultiYearCropData($startTime, $endTime, $cropId, $attr){
	$startYear = intval(substr($startTime, 0, 4));
	$endYear = intval(substr($endTime, 0, 4));

	$result = array();

	if($attr == 'amount_harvested'){
		$harvests = getHarvests($startTime, $endTime, $cropId);
		foreach($harvests as $h){
			$year = substr($h['startDate'], 0, 4);
			if(isset($result[$year])){
				$result[$year] += $h['quantity'];
			}else{
				$result[$year] = $h['quantity'];
			}
		}
	}elseif($attr == 'seeds_bought'){
		$seedPurchases = getSeedPurchases($startTime, $endTime, $cropId);
		foreach($seedPurchases as $s){
			$year = substr($s['startDate'], 0, 4);
			if(isset($result[$year])){
				$result[$year] += $s['quantity'];
			}else{
				$result[$year] = $s['quantity'];
			}
		}
	}elseif($attr == 'number_planted'){
		$plantsAdded = getPlantsAdded($startTime, $endTime, $cropId);
		foreach($plantsAdded as $p){
			$year = substr($p['startDate'], 0, 4);
			if(isset($result[$year])){
				$result[$year] += $p['quantity'];
			}else{
				$result[$year] = $p['quantity'];
			}
		}
	}elseif($attr == 'number_transplanted'){
		$plantsTransplanted = getPlantsTransplanted($startTime, $endTime, $cropId);
		foreach($plantsTransplanted as $p){
			$year = substr($p['startDate'], 0, 4);
			if(isset($result[$year])){
				$result[$year] += $p['quantity'];
			}else{
				$result[$year] = $p['quantity'];
			}
		}
	}elseif($attr == 'sales'){
		$soldDonated = getSDs($startTime, $endTime, $cropId);
		foreach($soldDonated as $s){
			$year = substr($s['starttime'], 0, 4);
			if(isset($result[$year])){
				$result[$year] += $s['cost'];
			}else{
				$result[$year] = $s['cost'];
			}
		}
	}elseif($attr == 'amount_donated'){
		$soldDonated = getSDs($startTime, $endTime, $cropId);
		foreach($soldDonated as $s){
			$year = substr($s['starttime'], 0, 4);
			if(isset($result[$year])){
				$result[$year] += $s['quantity'];
			}else{
				$result[$year] = $s['quantity'];
			}
		}
	}elseif($attr == 'pest_issues'){
		$allIssues = getPestWeedIssues($startTime, $endTime, $cropId);
		foreach($allIssues as $s){
			if($s['type'] == 0){
				$year = substr($s['startDate'], 0, 4);
				$result[$year] = 'Yes';
			}
		}
	}elseif($attr == 'weed_issues'){
		$allIssues = getPestWeedIssues($startTime, $endTime, $cropId);
		foreach($allIssues as $s){
			if($s['type'] == 1){
				$year = substr($s['startDate'], 0, 4);
				$result[$year] = 'Yes';
			}
		}
	}
	return $result;
}

/**
 * Get crop data within a year</br>
 * 
 * @param $year
 * 		four digit year
 * @param $cropId
 * 		cropId at database
 * @return
 * 		an array: $result[Type].
 * 		Type can be 'harvests', 'seedPurchases', 'plantsAdded', 'plantsTransplanted', 'revenue', 'locationPlanted', 'pestIssueExisted'.
 * 		Zero if no record.
 */
function getYearlyCropData($cropId, $year){
	$startTime = "$year-01-01";
	$endTime = "$year-12-31";
	$harvests = getHarvests($startTime, $endTime, $cropId);
	$seedPurchases = getSeedPurchases($startTime, $endTime, $cropId);
	$plantsAdded = getPlantsAdded($startTime, $endTime, $cropId);
	$plantsTransplanted = getPlantsTransplanted($startTime, $endTime, $cropId);
	$moneySold = getSDs($startTime, $endTime, $cropId, null);
	$pestWeedIssueExisted = getPestWeedIssues($startTime, $endTime, $cropId);
	$result = array('harvests' => 0, 
					'seedPurchases' => 0,
					'plantsAdded' => 0,
					'plantsTransplanted' => 0,
					'revenue' => 0,
					'pestWeedIssueExisted' => false,
					'locationPlanted' => null,
					);
	foreach($harvests as $h){
		$result['harvests'] += $h['quantity'];
	}
	foreach($seedPurchases as $s){
		$result['seedPurchases'] += $s['quantity'];
	}
	foreach($plantsAdded as $p){
		$result['plantsAdded'] += $p['quantity'];
	}
	foreach($plantsTransplanted as $p){
		$result['plantsTransplanted'] += $p['quantity'];
	}
	foreach($moneySold as $m){
		$result['revenue'] += $m['revenue'];
	}
	if($pestWeedIssueExisted != null){
		$result['pestWeedIssueExisted'] = true;
	}
	return $result;
}


/**
 * Make a range query.
 */
function rangeQuery($startTime, $endTime, $cropid, $tableName, $colName = "startDate"){
	$sql = "select * from $tableName ";
	$where = "WHERE";
	if($startTime != null){
		$sql = "$sql $where DATEDIFF($colName, '$startTime') >= 0 ";
		$where = "AND";
	}
	if($endTime != null){
		$sql = "$sql $where DATEDIFF($colName, '$endTime') <= 0 ";
		$where = "AND";
	}
	if($cropid != null){
		$sql = "$sql $where cropid = $cropid";
	}
	return $sql;
}
/**
 * user registeration or admin adds user
 * @param string $username
 * @param string $password
 * 			before MD5
 * @param tinyint $permission
 * @param string $email
 * @param string $notes
 */
function addUser($username, $password, $permission, $email, $notes){
	$con = connect_db();
	$temp = md5($password);
	$sql = "Insert into USER 
		(username, password, permission, email, notes) 
		values('$username','$temp',
		'$permission','$email','$notes')";
	if(mysql_query($sql, $con)){
		echo "reg ok!";
	}else{
		echo "reg failed!";
	}
	mysql_close($con);
}
/**
 * User login
 * @param $username
 * @param $password
 * 		before md5
 */
function userLogin($username, $password){
	$con = connect_db();
	$username= mysql_real_escape_string(stripslashes($_POST['username']));
	$password= mysql_real_escape_string(stripslashes($_POST['password']));
	$temp = md5($password);
	$sql="SELECT * FROM USER WHERE 
		username='$username' and 
		password='$temp'";
	$login = mysql_num_rows(mysql_query($sql, $con));
	/*
	if($count==1){
		echo "login ok!";
	}
	else {
		echo "login failed";
	}
	*/
	
	if (mysql_num_rows($login) == 1) {
        // Set username session variable
        $_SESSION['username'] = $_POST['username'];
        // Jump to secured page
        header('Location: securedpage.php');
	}
	else {
        // Jump to login page
        header('Location: index.php');
	}

	mysql_close($con);
}

/**
 * add new crop information
 * @param string $cropname
 * @param string $season
 * 		season in which the crop grows
 * @param string $notes
 * @param tinyint $categoryid
 */
function addCrop($cropname, $season, $notes, $categoryid){
	$con = connect_db();
	$sql = "insert into CROP 
		(cropname, season, notes, categoryid) values
		('$cropname', '$season', '$notes', '$categoryid')";
	if(mysql_query($sql, $con)){
		echo "add success!";
	}else{
		echo "add failed!";
	}
	mysql_close($con);
}
/**
 * get cropid by cropname
 * @return 
 * 		0 if not found
 * 		cropId if found
 */
function getCropId($cropname){
	$con = connect_db();
	$sql = "select * from CROP 
			where cropname = '$cropname'";
	$count = mysql_num_rows(mysql_query($sql, $con));
	if($count == 0){
		echo "No such cropname in record!";
		return 0;
	}
	$result = eatResult(mysql_query($sql, $con));
	mysql_close($con);
	return $result[0]['cropid'];
}

function getCropIds($crops){
	$result = array();
	foreach($crops as $crop){
		$result[] = getCropId($crop);
	}
	return $result;

}

function updateCrop($cropid, $cropname, $season, $notes, $categoryid){
	if($cropid == null) die("No cropid specified");
	$con = connect_db();
/*	$cropid = mysql_real_escape_string($cropid);
	$cropname = sanitize($cropname);
	$season = sanitize($season);
	$notes = sanitize($notes);
	$categoryid = sanitize($categoryid); */

	$sql = "update CROP
			SET	cropname=$cropname,
				season=$season,
				notes=$notes,
				categoryid=$categoryid
			WHERE	cropid=$cropid";

	if(mysql_query($sql, $con)){
		echo 'update ok!';
	}else{
		echo 'update failed.';
	}
	mysql_close($con);
}

function getCrop($cropid){
	if($cropid == null) die("No cropid specified");
	$con = connect_db();
	$cropid = mysql_real_escape_string($cropid);
	$sql = "select * from CROP where CROP.cropid=$cropid";
	$result = eatResult(mysql_query($sql, $con));
	mysql_close($con);
	if(count($result) < 1) die("No crop with cropid=$cropid found");
	
	return $result[0];
}

function getAllCrops(){
	$con = connect_db();
	$sql = "select * from CROP";
	$result = eatResult(mysql_query($sql, $con));
	mysql_close($con);
	return $result;
}

/**
 * add new category
 * @param string $categoryname
 * @param string $notes
 */
function addCategory($categoryname, $notes){
	$con = connect_db();
	$sql = "insert into CATEGORY 
			(categoryname, notes) values
			('$categoryname', '$notes')";
	if(mysql_query($sql, $con)){
		echo 'add ok!';
	}else{
		echo 'add failed.';
	}
	mysql_close($con);
}

function getCategory($categoryid){
	if($categoryid == null) die("No categoryid specified");
	$con = connect_db();
	$categoryid = mysql_real_escape_string($categoryid);
	$sql = "select * from CATEGORY where categoryid=$categoryid";
	$result = eatResult(mysql_query($sql, $con));
	mysql_close($con);
	if(count($result) < 1) die("No category with catetoryid=$categoryid found");
	
	return $result[0];
}

function getAllCategories(){
	$con = connect_db();
	$sql = "select * from CATEGORY";
	$result = eatResult(mysql_query($sql, $con));
	mysql_close($con);
	return $result;
}

function updateCategory($categoryid, $categoryname, $notes){
	if($categoryid == null) die("No categoryid specified");
	$con = connect_db();

	$sql = "update CATEGORY
			SET	categoryname=$categoryname,
				notes=$notes
			WHERE	categoryid=$categoryid";

	if(mysql_query($sql, $con)){
		echo 'update ok!';
	}else{
		echo 'update failed.';
	}
	mysql_close($con);
}

function addSeedPurchase($cropid, $quantity, $startDate, $cost, $notes){
	$con = connect_db();
	$sql = "insert into SEEDS_PURCHASED values(
			'$cropid', '$startDate', '$quantity', '$notes', '$cost')";
	if(mysql_query($sql, $con)){
		echo 'add ok!';
	}else{
		echo 'add failed.';
	}
	mysql_close($con);
}

/*
 * Get all seed purchases in a given range of time.
 *
 * @param date $startTime earliest time in the range. If null, the beginning of the range is not constrained
 * @param date $endTime latest time in the range. If null, the end of the range is not constrained
 * @param int $cropid crop whose seed purchases to return. If null, return purchases for all crops
 */
function getSeedPurchases($startTime, $endTime, $cropid){
	$con = connect_db();
	$sql = rangeQuery($startTime, $endTime, $cropid, "SEEDS_PURCHASED");
	$result = eatResult(mysql_query($sql, $con));
	mysql_close($con);
	return $result;
}

function updateSeedPurchase($cropid, $quantity, $startDate, $cost, $notes){
	$con = connect_db();
	$sql = "update SEEDS_PURCHASED
			SET	quantity=$quantity,
				notes=$notes,
				cost=$cost
			WHERE cropid=$cropid
				AND startDate=$startDate";
	if(mysql_query($sql, $con)){
		echo 'update ok!';
	}else{
		echo 'update failed.';
	}
	mysql_close($con);
}

/**
 * add purchase plan
 * @param float $quantity
 * @param date $time
 * @param float $cost
 */
/*
function addPurchasePlan($cropname, $quantity, $time, $cost, $notes){
	$con = connect_db();
	$cropid = getCropId($cropname);
	$sql = "insert into PURCHASE_PLAN values(
			'$cropid', '$time', '$quantity', '$notes', '$cost')";
	if(mysql_query($sql, $con)){
		echo 'add ok!';
		//todo: add success
	}else{
		echo 'add failed.';
		//todo add failed
	}
	mysql_close($con);
} */
/**
 * add planting history
 * @param float $quantity
 * @param date $time
 * @param float $cost
 * $param string $location 
 */
function addPlantAdded($cropid, $quantity, $startDate, $notes){
	$con = connect_db();
	$sql = "insert into PLANTS_ADDED values(
			'$cropid', '$startDate', '$quantity', '$notes')";
	if(mysql_query($sql, $con)){
		echo 'add ok!';
	}else{
		echo 'add failed.';
	}
	mysql_close($con);
}
function addPlantsTransplanted($cropid, $quantity, $startDate, $notes){
	$con = connect_db();
	$sql = "insert into PLANTS_TRANSPLANTED values(
			'$cropid', '$startDate', '$quantity', '$notes')";
	if(mysql_query($sql, $con)){
		echo 'add ok!';
	}else{
		echo 'add failed.';
	}
	mysql_close($con);
}
/*
 * Get all plants added in a given range of time.
 *
 * @param date $startTime earliest time in the range. If null, the beginning of the range is not constrained
 * @param date $endTime latest time in the range. If null, the end of the range is not constrained
 * @param int $cropid crop whose seed purchases to return. If null, return purchases for all crops
 */
function getPlantsAdded($startTime, $endTime, $cropid){
	$con = connect_db();
	$sql = rangeQuery($startTime, $endTime, $cropid, "PLANTS_ADDED");
	$result = eatResult(mysql_query($sql, $con));
	mysql_close($con);
	return $result;
}
function getPlantsTransplanted($startTime, $endTime, $cropid){
	$con = connect_db();
	$sql = rangeQuery($startTime, $endTime, $cropid, "PLANTS_TRANSPLANTED");
	$result = eatResult(mysql_query($sql, $con));
	mysql_close($con);
	return $result;
}
/**
 * update plants added
 * @param $cropid
 * @param $quantity
 * @param $time
 * @param $notes
 * @param $location
 */
function updatePlantsAdded($cropid, $quantity, $startDate, $notes){
	$con = connect_db();
	$sql = "update PLANTS_ADDED
			SET	quantity=$quantity,
				notes=$notes
			WHERE cropid=$cropid
				AND startDate=$startDate";
	if(mysql_query($sql, $con)){
		echo 'update ok!';
	}else{
		echo 'update failed.';
	}
	mysql_close($con);
}
function updatePlantsTransplanted($cropid, $quantity, $startDate, $notes){
	$con = connect_db();
	$sql = "update PLANTS_TRANSPLANTED
			SET	quantity=$quantity,
				notes=$notes,
			WHERE cropid=$cropid
				AND startDate=$startDate";
	if(mysql_query($sql, $con)){
		echo 'update ok!';
	}else{
		echo 'update failed.';
	}
	mysql_close($con);
}
/**
 * add havest history
 * @param float $quantity
 * @param date $time
 * @param float $cost
 * $param string $location 
 */
function addHarvest($cropid, $quantity, $startDate, $notes){
	$con = connect_db();
	$sql = "insert into AMOUNT_HARVESTED values(
			'$cropid', '$startDate', '$quantity', '$notes')";
	if(mysql_query($sql, $con)){
		echo 'add ok!';
	}else{
		echo 'add failed.';
	}
	mysql_close($con);
}
/**
 * Get all harvests in a given range of time.
 *
 * @param date $startTime 
 * 		earliest time in the range. If null, the beginning of the range is not constrained
 * @param date $endTime 
 * 		latest time in the range. If null, the end of the range is not constrained
 * @param int $cropid 
 * 		crop whose seed purchases to return. If null, return purchases for all crops
 */
function getHarvests($startTime, $endTime, $cropid){
	$con = connect_db();
	$sql = rangeQuery($startTime, $endTime, $cropid, "AMOUNT_HARVESTED");
	$result = eatResult(mysql_query($sql, $con));
	mysql_close($con);
	return $result;
}

function updateHarvests($cropid, $quantity, $startDate, $notes){
	$con = connect_db();
	$sql = "update AMOUNT_HARVESTED
			SET	quantity=$quantity,
				notes=$notes
			WHERE cropid=$cropid
			AND startDate=$startDate";
	if(mysql_query($sql, $con)){
		echo 'update ok!';
	}else{
		echo 'update failed.';
	}
	mysql_close($con);
}

/**
 * add customer info
 */
function addCustomer($customername, $notes){
	$con = connect_db();
	$sql = "insert into CUSTOMERS 
			($customername, $notes) values
			('$customername', '$notes')";
	if(mysql_query($sql, $con)){
		echo 'add ok!';
	}else{
		echo 'add failed.';
	}
	mysql_close($con);
}

function getCustomer($customerid){
	if($customerid == null) die("No customerid specified");
	$con = connect_db();
	$customerid = mysql_real_escape_string($customerid);
	$sql = "select * from CUSTOMER where customerid=$customerid";
	$result = eatResult(mysql_query($sql, $con));
	mysql_close($con);
	if(count($result) < 1) die("No customer with customerid=$customerid found");
	return $result[0];
}

function getAllCustomers(){
	$con = connect_db();
	$sql = "select * from CUSTOMER";
	$result = eatResult(mysql_query($sql, $con));
	mysql_close($con);
	return $result;
}

function updateCustomer($customerid, $customername, $notes){
	if($customerid == null) die("No customerid specified");
	$con = connect_db();

	$sql = "update CUSTOMER
			SET	customername=$customername,
				notes=$notes
			WHERE	customerid=$customerid";

	if(mysql_query($sql, $con)){
		echo 'update ok!';
		//todo: update success
	}else{
		echo 'update failed.';
		//todo update failed
	}
	mysql_close($con);
}


function addOrderLog($cropid, $quantity, $startDate, $notes, $customerid){
	$con = connect_db();
	$sql = "insert into ORDER_LOG values(
			'$cropid', '$startDate', '$quantity', '$notes', '$customerid')";
	if(mysql_query($sql, $con)){
		echo 'add ok!';
	}else{
		echo 'add failed.';
	}
	mysql_close($con);
}

function getOrderLog($startTime, $endTime, $cropid){
	$con = connect_db();
	$sql = rangeQuery($startTime, $endTime, $cropid, "ORDER_LOG");
	$result = eatResult(mysql_query($sql, $con));
	mysql_close($con);
	return $result;
}
function updateOrderLog($cropid, $quantity, $startDate, $notes, $customerid){
	$con = connect_db();
	$sql = "update ORDER_LOG
			SET	quantity=$quantity,
				notes=$notes
			WHERE cropid=$cropid
				AND startDate=$startDate
				AND customerid=$customerid";
	if(mysql_query($sql, $con)){
		echo 'add ok!';
	}else{
		echo 'add failed.';
	}
	mysql_close($con);
}

function addSDLog($cropid, $quantity, $startDate, $revenue, $notes, $customerid){
	$con = connect_db();
	$sql = "insert into AMOUNT_SOLD_DONATED values(
			'$cropid', '$startDate', '$quantity', '$revenue', '$notes', '$customerid')";
	if(mysql_query($sql, $con)){
		echo 'add ok!';
	}else{
		echo 'add failed.';
	}
	mysql_close($con);
}

/**
 * Get all sales/donations in a given range of time.
 *
 * @param date $startTime 
 * 		earliest time in the range. If null, the beginning of the range is not constrained
 * @param date $endTime 
 * 		latest time in the range. If null, the end of the range is not constrained
 * @param int $cropid 
 * 		crop whose seed purchases to return. If null, return for all crops
 */
function getSDs($startTime, $endTime, $cropid, $customerid = null){
	$con = connect_db();
	$sql = rangeQuery($startTime, $endTime, $cropid, "AMOUNT_SOLD_DONATED", "starttime");

	if($customerid != null){
		if($startTime != null || $endTime != null || $cropid != null){
			$sql = "$sql AND customerid=$customerid";
		}else{
			$sql = "$sql WHERE customerid=$customerid";
		}
	}
	$result = eatResult(mysql_query($sql, $con));
	mysql_close($con);
	return $result;
}

function updateSDs($cropid, $quantity, $startDate, $revenue, $notes, $customerid){
	$con = connect_db();
	$sql = "update AMOUNT_SOLD_DONATED
			SET	quantity=$quantity,
				revenue=$revenue,
				notes=$notes
			WHERE cropid=$cropid
				AND startDate=$startDate
				AND customerid=$customerid";
	if(mysql_query($sql, $con)){
		echo 'update ok!';
	}else{
		echo 'update failed!';
	}
	mysql_close($con);
}
function addPestLog($cropid, $type, $startDate, $notes, $location){
	$con = connect_db();
	$sql = "insert into PEST_WEED_LOG values(
			'$cropid', '$type', '$startDate', '$notes', '$location')";
	if(mysql_query($sql, $con)){
		echo 'add ok!';
	}else{
		echo 'add failed.';
	}
	mysql_close($con);
}
/**
 * Get all pest logs in a given range of time.
 *
 * @param date $startTime 
 * 		earliest time in the range. If null, the beginning of the range is not constrained
 * @param date $endTime 
 * 		latest time in the range. If null, the end of the range is not constrained
 * @param int $cropid 
 * 		crop whose seed purchases to return. If null, return for all crops
 */
function getPestWeedIssues($startTime, $endTime, $cropid){
	$con = connect_db();
	$sql = rangeQuery($startTime, $endTime, $cropid, "PEST_WEED_LOG");
	$result = eatResult(mysql_query($sql, $con));
	mysql_close($con);
	return $result;
}

function updatePestLogs($cropid, $type, $startDate, $notes, $location){
	$con = connect_db();
	$sql = "update PEST_WEED_LOG
			SET	type=$type,
				notes=$notes,
				location=$location
			WHERE cropid=$cropid
				AND startDate=$startDate;";
	if(mysql_query($sql, $con)){
		echo 'updates ok!';
	}else{
		echo 'updates failed!';
	}
	mysql_close($con);
}
